﻿using Dapper;
using Dapper.Contrib.Extensions;
using IntelligentGateway.Model.Interface;
using IntelligentGateway.Tools;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace IntelligentGateway.Data
{
    public class CommDA<T> where T : class, IBase
    {
        
        public virtual string Delete(T t)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    bool success = conn.Delete<T>(t);
                    return success ? "success" : "fail";
                }
                catch (Exception e)
                {
                    return "fail:" + e.ToString();
                }
            }
        }
        public virtual string DeleteAll()
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    bool success = conn.DeleteAll<T>();
                    return success ? "success" : "fail";
                }
                catch (Exception e)
                {
                    return "fail:" + e.ToString();
                }
            }
        }
        public virtual string Insert(T t, bool IsAutoInc)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    long rows = conn.Insert<T>(t);
                    if (IsAutoInc)
                    {
                        t.ID = (int)rows;
                        return "success:" + rows;
                    }
                    else return "success:" + t.ID;
                }
                catch (Exception e)
                {
                    return "fail:" + e.ToString();
                }
            }
        }
        public virtual T InsertT(T t)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    long rows = conn.Insert<T>(t);
                    return t;
                }
                catch (Exception e)
                {
                    return null;
                }
            }
        }
        public virtual string Insert(T t, bool IsAutoInc, IDbConnection conn, IDbTransaction transaction)
        {
            try
            {
                long rows = conn.Insert<T>(t, transaction);
                if (IsAutoInc)
                {
                    return "success:" + rows;
                }
                else return "success:" + t.ID;
            }
            catch (Exception e)
            {
                return "fail:" + e.ToString();
            }

        }
        public virtual string InsertList(string strSql, List<T> list, IDbConnection conn, IDbTransaction transaction)
        {
            try
            {
                long rows = conn.Execute(strSql, list, transaction);
                return "success:" + rows;
            }
            catch (Exception e)
            {
                return "fail:" + e.ToString();
            }

        }
        public virtual string InsertList(string strSql, List<T> list)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                if (conn.State == ConnectionState.Closed) { conn.Open(); }
                IDbTransaction dbTransaction = conn.BeginTransaction();
                try
                {
                    conn.DeleteAll<T>();
                    long rows = conn.Execute(strSql, list, dbTransaction);
                    dbTransaction.Commit();
                    return "success:" + rows;
                }
                catch (Exception e)
                {
                    dbTransaction.Rollback();
                    return "fail:" + e.ToString();
                }
            }

        }
        public System.Collections.Generic.List<T> SelectAllByFilter(T filter, int startindex, int endindex, ref int totalrecords)
        {
            string strsql = SqlBuilderTools.GenarateSqlStr(filter, "", false, startindex, endindex);
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    List<T> lst = null;
                    if (conn.State == ConnectionState.Closed) { conn.Open(); }
                    if (filter.TotalRecords <= 0 && startindex >= 0)
                    {
                        var reader = conn.QueryMultiple(strsql);
                        lst = reader.Read<T>().AsList();
                        totalrecords = reader.ReadSingle<int>();
                    }
                    else
                    {
                        var data = conn.Query<T>(strsql);
                        if (data == null) return new List<T>();
                        else return data.AsList();
                    }
                    return lst;
                }
                catch (Exception e)
                {
                    return new List<T>();
                }
            }
        }
        public T SelectMatchByFilter(T filter)
        {
            string strsql = SqlBuilderTools.GenarateSqlStr(filter, "", true);
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    T t = conn.QueryFirstOrDefault<T>(strsql.ToString());
                    return t;
                }
                catch (Exception e)
                {
                    return null;
                }
            }

        }
        public System.Collections.Generic.List<T> SelectMatchAllByFilter(T filter, int startindex, int endindex, ref int totalrecords)
        {
            string strsql = SqlBuilderTools.GenarateSqlStr(filter, "", true, startindex, endindex);
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    List<T> lst = null;
                    if (conn.State == ConnectionState.Closed) { conn.Open(); }
                    if (filter.TotalRecords <= 0 && startindex >= 0)
                    {
                        var reader = conn.QueryMultiple(strsql);
                        lst = reader.Read<T>().AsList();
                        totalrecords = reader.ReadSingle<int>();
                    }
                    else
                    {
                        var data = conn.Query<T>(strsql);
                        if (data == null) return new List<T>();
                        else return data.AsList();
                    }
                    return lst;
                }
                catch (Exception e)
                {
                    return new List<T>();
                }
            }
        }

        public System.Collections.Generic.List<T> SelectAllData()
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {                    
                    IEnumerable<T> lst = conn.GetAll<T>();
                    return lst.AsList();
                }
                catch (Exception e)
                {
                    return new List<T>();
                }
            }
        }

        public T SelectByFilter(T filter)
        {
            string strsql = SqlBuilderTools.GenarateSqlStr(filter, "");
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    T t = conn.QueryFirstOrDefault<T>(strsql.ToString());
                    return t;
                }
                catch (Exception e)
                {
                    return null;
                }
            }

        }

        public T SelectByKey(int key)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    T t = conn.Get<T>(key);
                    return t;
                }
                catch (Exception e)
                {
                    return default(T);
                }
            }
        }

        public int GetTotalRecords(T filter)
        {
            string strsql = SqlBuilderTools.GenarateTotalRecordsStr(filter, "");
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    int nums = conn.ExecuteScalar<int>(strsql);
                    return nums;
                }
                catch (Exception e)
                {
                    return 0;
                }
            }
        }

        public virtual string Update(T t)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    bool success = conn.Update<T>(t);
                    return success ? "success" : "fail";
                }
                catch (Exception e)
                {
                    return "fail:" + e.ToString();
                }
            }
        }
        public virtual string Update(T t, IDbConnection conn, IDbTransaction transaction)
        {
            try
            {
                bool success = conn.Update<T>(t, transaction);
                return "success";
            }
            catch (Exception e)
            {
                return "fail:" + e.ToString();
            }

        }
        public virtual string UpdateDemand(T t)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    bool success = conn.UpdateDemand<T>(t);
                    return success ? "success" : "fail";
                }
                catch (Exception e)
                {
                    return "fail:" + e.ToString();
                }
            }
        }
        public virtual string UpdateDemand(T t, IDbConnection conn, IDbTransaction transaction)
        {
            try
            {
                bool success = conn.UpdateDemand<T>(t, transaction);
                return "success";
            }
            catch (Exception e)
            {
                return "fail:" + e.ToString();
            }
        }
        public virtual string DeleteDemand(T t)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    bool success = conn.DeleteDemand<T>(t);
                    return success ? "success" : "fail";
                }
                catch (Exception e)
                {
                    return "fail:" + e.ToString();
                }
            }
        }
    }
    public class CommDA
    {
        public static int GenNextNum(int ProjectId, string TypeId)
        {
            using (var conn = DataFactory.GetMYSQLConnection())
            {
                try
                {
                    int number = conn.ExecuteScalar<int>("select getflowid(" + ProjectId + ",'" + TypeId + "')");                    
                    return number;
                }
                catch (Exception e)
                {
#if DEBUG
                    Log.Debug("GenNextNum:" + e.ToString());
#endif
                    return 0;
                }
            }
        }
    }
}
